Перейти к основному содержимому

3.08. Справочник по Microsoft SQL Server

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Справочник по Microsoft SQL Server​

1. Основные компоненты SQL Server​

1.1. Ядро СУБД (Database Engine)​

  • Отвечает за хранение, обработку и безопасность данных.
  • Поддерживает транзакции, репликацию, зеркальное отображение, Always On.
  • Работает в режимах: Windows Authentication, Mixed Mode (Windows + SQL Auth).

1.2. SQL Server Management Studio (SSMS)​

  • Графический клиент для управления экземплярами SQL Server.
  • Версии: SSMS 18.x, 19.x — совместимы с SQL Server 2016–2022.
  • Включает:
    • Object Explorer
    • Query Editor
    • Activity Monitor
    • Profiler (через расширения или Azure Data Studio)
    • Template Explorer
    • Registered Servers

1.3. T-SQL (Transact-SQL)​

  • Расширение стандарта SQL от Microsoft.
  • Поддерживает процедурное программирование, переменные, поток управления, обработку ошибок.
  • Используется во всех операциях: DDL, DML, DCL, TCL.

2. Типы данных SQL Server​

2.1. Числовые​

ТипДиапазонПримечание
BIT0, 1, NULLЛогическое значение
TINYINT0–2551 байт
SMALLINT-32 768 – 32 7672 байта
INT-2 147 483 648 – 2 147 483 6474 байта
BIGINT-2⁶³ – 2⁶³-18 байт
DECIMAL(p,s) / NUMERIC(p,s)До 38 цифрТочная дробь
MONEY-922 337 203 685 477.5808 – +922 337 203 685 477.58078 байт
SMALLMONEY-214 748.3648 – +214 748.36474 байта
FLOAT(n)Прибл. ±1.79E+308n = 1–53 (точность)
REALFLOAT(24)4 байта

2.2. Строковые​

ТипМакс. длинаЮникодПримечание
CHAR(n)8000НетФиксированная длина
VARCHAR(n)8000НетПеременная длина
VARCHAR(MAX)2³¹-1НетLOB-тип
NCHAR(n)4000ДаФиксированная, UTF-16
NVARCHAR(n)4000ДаПеременная, UTF-16
NVARCHAR(MAX)2³¹-1ДаLOB-тип
TEXTУстаревшийНетНе использовать
NTEXTУстаревшийДаНе использовать

2.3. Дата и время​

ТипДиапазонТочностьРазмер
DATE0001-01-01 – 9999-12-311 день3 байта
TIME(p)00:00:00.0000000 – 23:59:59.9999999100 нс3–5 байт
DATETIME2(p)0001-01-01 – 9999-12-31100 нс6–8 байт
DATETIME1753-01-01 – 9999-12-313.33 мс8 байт
SMALLDATETIME1900-01-01 – 2079-06-061 мин4 байта
DATETIMEOFFSET(p)Как DATETIME2 + часовой пояс100 нс8–10 байт

2.4. Бинарные​

ТипМакс. длинаПримечание
BINARY(n)8000Фиксированная
VARBINARY(n)8000Переменная
VARBINARY(MAX)2³¹-1LOB-тип
IMAGEУстаревшийНе использовать

2.5. Специальные​

  • UNIQUEIDENTIFIER — GUID (16 байт)
  • SQL_VARIANT — хранит любой тип (кроме LOB и TIMESTAMP)
  • TIMESTAMP / ROWVERSION — автоматически генерируемый номер версии строки
  • HIERARCHYID — древовидная структура
  • GEOMETRY, GEOGRAPHY — пространственные данные
  • XML — типизированный или нетипизированный XML

3. Объекты базы данных​

3.1. Таблицы​

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • Поддержка:
    • Identity (IDENTITY(1,1))
    • Computed columns
    • Constraints (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, DEFAULT)
    • Filegroups
    • Partitioning

3.2. Представления (Views)​

  • Виртуальные таблицы на основе запроса.
  • CREATE VIEW, ALTER VIEW, DROP VIEW
  • WITH SCHEMABINDING — привязка к схеме
  • Обновляемые представления при соблюдении условий

3.3. Хранимые процедуры​

CREATE PROCEDURE dbo.GetUsers
    @Status INT = 1
AS
BEGIN
    SELECT * FROM Users WHERE Status = @Status;
END
  • Поддержка входных/выходных параметров
  • EXEC / EXECUTE
  • sp_executesql — для динамического SQL с параметрами

3.4. Функции​

Скалярные​

CREATE FUNCTION dbo.GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END

Табличные​

  • Inline (возвращают одно выражение SELECT)
  • Multi-statement (создают временную таблицу внутри)

3.5. Триггеры​

  • AFTER / INSTEAD OF
  • На INSERT, UPDATE, DELETE
  • Доступ к inserted и deleted виртуальным таблицам

3.6. Индексы​

  • Кластеризованный — определяет физический порядок строк (один на таблицу)
  • Некластеризованный — до 999 на таблицу (SQL Server 2016+)
  • Columnstore — для аналитических нагрузок
  • Filtered — индекс с условием (WHERE Status = 1)
  • Unique, Composite, Included columns

3.7. Синонимы (Synonyms)​

  • Алиасы для объектов: CREATE SYNONYM dbo.Users FOR RemoteDB.dbo.Users

3.8. Последовательности (Sequences)​

  • Альтернатива IDENTITY
  • CREATE SEQUENCE seq_UserID START WITH 1 INCREMENT BY 1

3.9. Типы данных (User-Defined Types)​

  • CREATE TYPE PhoneNumber FROM VARCHAR(20)
  • CREATE TYPE UserTableType AS TABLE (...)

4. Системные функции T-SQL​

4.1. Строковые​

  • LEN(), DATALENGTH()
  • SUBSTRING(), LEFT(), RIGHT()
  • REPLACE(), STUFF()
  • TRIM(), LTRIM(), RTRIM()
  • UPPER(), LOWER()
  • CONCAT(), FORMAT()
  • STRING_AGG() (SQL Server 2017+)
  • STRING_SPLIT() (возвращает таблицу)

4.2. Числовые​

  • ABS(), CEILING(), FLOOR()
  • ROUND(), POWER(), SQRT()
  • RAND(), CHECKSUM()
  • ISNULL(), COALESCE()

4.3. Дата и время​

  • GETDATE(), GETUTCDATE()
  • SYSDATETIME(), SYSUTCDATETIME()
  • DATEADD(), DATEDIFF(), DATEDIFF_BIG()
  • DATENAME(), DATEPART()
  • EOMONTH(), ISDATE()

4.4. Системные​

  • @@VERSION, @@SERVERNAME, @@SERVICENAME
  • @@ROWCOUNT, @@ERROR, @@IDENTITY, SCOPE_IDENTITY()
  • USER_NAME(), SUSER_SNAME(), IS_MEMBER()
  • OBJECT_ID(), OBJECT_NAME()
  • HAS_PERMS_BY_NAME()

4.5. JSON (SQL Server 2016+)​

  • JSON_VALUE(), JSON_QUERY()
  • ISJSON(), JSON_MODIFY()
  • FOR JSON PATH/AUTO

4.6. XML​

  • value(), query(), exist(), modify(), nodes()

5. Операторы и конструкции T-SQL​

5.1. DDL​

  • CREATE, ALTER, DROP
  • TRUNCATE TABLE
  • RENAME — через sp_rename

5.2. DML​

  • SELECT, INSERT, UPDATE, DELETE, MERGE
  • OUTPUT clause — возвращает изменённые строки
  • TOP (n), OFFSET-FETCH (SQL Server 2012+)

5.3. Управление потоком​

  • IF ... ELSE
  • WHILE
  • BEGIN ... END
  • TRY ... CATCH
  • THROW (SQL Server 2012+)
  • WAITFOR DELAY / TIME

5.4. Курсоры​

  • DECLARE cursor_name CURSOR FOR SELECT ...
  • OPEN, FETCH, CLOSE, DEALLOCATE
  • Использовать только при крайней необходимости

5.5. Динамический SQL​

  • EXEC('SELECT * FROM ' + @table)
  • sp_executesql N'SELECT * FROM Users WHERE ID = @id', N'@id INT', @id = 5

6. Безопасность и права​

6.1. Пользователи и логины​

  • CREATE LOGIN — на уровне сервера
  • CREATE USER — на уровне БД
  • ALTER ROLE db_datareader ADD MEMBER user1

6.2. Разрешения​

  • GRANT SELECT ON dbo.Users TO user1
  • DENY DELETE ON SCHEMA::dbo TO user1
  • REVOKE INSERT ON OBJECT::Orders FROM user1

6.3. Схемы​

  • CREATE SCHEMA reporting
  • По умолчанию: dbo
  • Объекты: schema.object

6.4. Аудит​

  • SQL Server Audit (на уровне экземпляра или БД)
  • Журналы в Windows Event Log или файл

7. Конфигурация SQL Server​

7.1. sp_configure​

  • max server memory (MB)
  • min server memory (MB)
  • max degree of parallelism (MAXDOP)
  • cost threshold for parallelism
  • remote query timeout
  • default trace enabled
  • clr enabled — для CLR-интеграции

Пример:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 8192;
RECONFIGURE;

7.2. Database Scoped Configuration (SQL Server 2016+)​

  • ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
  • LEGACY_CARDINALITY_ESTIMATION = ON
  • PARAMETER_SNIFFING = OFF
  • QUERY_STORE = ON

7.3. Query Store​

  • Хранит планы выполнения и статистику запросов
  • Включается: ALTER DATABASE MyDB SET QUERY_STORE = ON;
  • Полезен для анализа регрессий производительности

8. Производительность и мониторинг​

8.1. Динамические административные представления (DMVs)​

  • sys.dm_exec_requests — активные запросы
  • sys.dm_exec_sessions — сессии
  • sys.dm_os_wait_stats — статистика ожиданий
  • sys.dm_db_index_usage_stats — использование индексов
  • sys.dm_exec_query_stats — статистика по запросам
  • sys.dm_io_virtual_file_stats — I/O по файлам

8.2. Extended Events​

  • Лёгкая замена SQL Profiler
  • Создаются через SSMS или T-SQL
  • События: sql_statement_completed, rpc_completed, wait_info

8.3. Планы выполнения​

  • Actual vs Estimated Execution Plan
  • Операторы: Index Seek, Scan, Nested Loops, Hash Match, Sort
  • Warnings: Missing Index, Implicit Conversion, Spill to TempDB

9. Резервное копирование и восстановление​

9.1. Типы резервных копий​

  • Full — полная копия
  • Differential — изменения с момента последнего Full
  • Transaction Log — только в модели восстановления Full или Bulk-Logged

9.2. Команды​

BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB.bak';
BACKUP LOG MyDB TO DISK = 'C:\Backups\MyDB.trn';
RESTORE DATABASE MyDB FROM DISK = 'C:\Backups\MyDB.bak' WITH REPLACE;

9.3. Модели восстановления​

  • Simple — лог усекается автоматически
  • Full — поддержка point-in-time recovery
  • Bulk-Logged — оптимизация массовых операций

10. SSMS: ключевые возможности​

10.1. Горячие клавиши​

  • Ctrl + E — выполнить запрос
  • Ctrl + L — показать план выполнения
  • Ctrl + R — скрыть/показать результаты
  • Alt + F1 — sp_help для выделенного объекта

10.2. Шаблоны (Template Explorer)​

  • Ctrl + Alt + T — открыть
  • Готовые шаблоны: создание таблицы, процедуры, индекса

10.3. Настройки​

  • Tools → Options → Text Editor → Transact-SQL
    • IntelliSense
    • Auto list members
    • Parameter information
  • Results to Grid / Text / File

10.4. Activity Monitor​

  • Процессы, ожидания, ресурсы, дорогостоящие запросы

11. Агрегатные и оконные функции​

11.1. Стандартные агрегатные функции​

  • COUNT(), COUNT_BIG()
  • SUM(), AVG()
  • MIN(), MAX()
  • STDEV(), VAR()
  • CHECKSUM_AGG()
  • GROUPING(), GROUPING_ID() — для ROLLUP/CUBE

11.2. Оконные функции (SQL Server 2012+)​

Синтаксис:

function OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ROWS|RANGE frame_clause]]
)

Ранжирование​

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • NTILE(n)

Агрегация по окну​

  • SUM(Sales) OVER (PARTITION BY Region ORDER BY Month)
  • AVG(Price) OVER (ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Смещение​

  • LAG(column, offset, default) — предыдущая строка
  • LEAD(column, offset, default) — следующая строка
  • FIRST_VALUE(), LAST_VALUE()

Статистика​

  • PERCENT_RANK()
  • CUME_DIST()
  • PERCENTILE_CONT(), PERCENTILE_DISC()

12. Временные структуры данных​

12.1. Локальные временные таблицы​

  • Префикс: #
  • Жизнь: до конца сессии или явного DROP
  • Хранятся в tempdb
  • Поддерживают индексы, ограничения, статистику
CREATE TABLE #TempUsers (ID INT, Name NVARCHAR(100));
INSERT INTO #TempUsers VALUES (1, 'Alice');

12.2. Глобальные временные таблицы​

  • Префикс: ##
  • Доступны всем сессиям
  • Удаляются, когда последняя сессия завершает работу с ними

12.3. Табличные переменные​

  • Объявляются как DECLARE @t TABLE (...)
  • Не имеют статистики (планы могут быть неточными)
  • Хранятся в памяти при малом объёме, иначе — в tempdb
  • Не поддерживают DDL после объявления

12.4. Табличные выражения​

CTE (Common Table Expression)​

  • Временный именованный результат внутри одного запроса
  • Рекурсивные CTE поддерживают иерархические запросы
WITH EmpCTE AS (
    SELECT ID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ID, e.Name, e.ManagerID, c.Level + 1
    FROM Employees e
    INNER JOIN EmpCTE c ON e.ManagerID = c.ID
)
SELECT * FROM EmpCTE;

Производные таблицы​

  • Подзапрос в FROM: SELECT * FROM (SELECT ...) AS d

13. Транзакции и изоляция​

13.1. Уровни изоляции​

  • READ UNCOMMITTED — разрешает «грязное» чтение
  • READ COMMITTED — по умолчанию; читает только зафиксированные данные
  • REPEATABLE READ — блокирует строки до конца транзакции
  • SERIALIZABLE — блокирует диапазоны, предотвращает фантомные чтения
  • SNAPSHOT — использует версионирование строк в tempdb

Включение:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

13.2. Управление транзакциями​

  • BEGIN TRANSACTION
  • COMMIT TRANSACTION
  • ROLLBACK TRANSACTION
  • SAVE TRANSACTION savepoint_name — точка сохранения

13.3. Блокировки и мёртвые блокировки​

  • Типы блокировок: S (shared), X (exclusive), U (update), IX, IS, Sch-S, Sch-M
  • sp_who2, sys.dm_tran_locks — диагностика
  • Приоритет: SET DEADLOCK_PRIORITY HIGH/LOW/NORMAL

14. Расширенные возможности SQL Server​

14.1. Full-Text Search​

  • Индексация текста для семантического поиска
  • Функции: CONTAINS(), FREETEXT(), CONTAINSTABLE(), FREETEXTTABLE()
  • Создание: CREATE FULLTEXT INDEX ON Articles(Content) KEY INDEX PK_Articles

14.2. Service Broker​

  • Встроенная система асинхронной очередной обработки
  • Объекты: MESSAGE TYPE, CONTRACT, QUEUE, SERVICE
  • Используется для триггерных уведомлений, фоновых задач

14.3. CLR Integration​

  • Выполнение управляемого кода (.NET) внутри SQL Server
  • Требует ALTER DATABASE MyDB SET TRUSTWORTHY ON или подписанные сборки
  • Объекты: скалярные функции, хранимые процедуры, триггеры на C#/VB.NET

14.4. PolyBase (SQL Server 2016+)​

  • Запросы к внешним данным: Hadoop, Azure Blob Storage, Oracle, Teradata
  • Создание внешних источников, форматов, таблиц

14.5. Machine Learning Services (SQL Server 2017+)​

  • Выполнение скриптов Python/R внутри SQL Server
  • Функция: sp_execute_external_script
  • Требует отдельной установки компонента

14.6. Graph Database (SQL Server 2017+)​

  • Таблицы с атрибутами AS NODE и AS EDGE
  • Специальный синтаксис: MATCH (a)-[e]->(b)

15. Администрирование и обслуживание​

15.1. Системные представления​

  • sys.databases — список БД
  • sys.tables, sys.views, sys.procedures
  • sys.columns, sys.indexes, sys.foreign_keys
  • sys.sql_modules — текст определений процедур/функций
  • sys.configurations — параметры сервера

15.2. Хранимые процедуры управления​

  • sp_help — метаданные объекта
  • sp_who, sp_who2 — активные процессы
  • sp_configure — настройка сервера
  • sp_rename — переименование объекта
  • sp_spaceused — использование пространства
  • sp_updatestats — обновление статистики
  • sp_recompile — сброс плана выполнения

15.3. Обслуживание индексов​

  • ALTER INDEX ... REBUILD — полная перестройка
  • ALTER INDEX ... REORGANIZE — дефрагментация
  • Автоматизация через Maintenance Plans или SQL Agent Jobs

15.4. SQL Server Agent​

  • Планировщик заданий
  • Шаги: T-SQL, PowerShell, CmdExec, SSIS
  • Уведомления: почта, события Windows
  • Прокси-учётные записи для безопасного выполнения

16. Развертывание и совместимость​

16.1. Версии SQL Server​

  • Express — бесплатная, ограничена: 10 ГБ БД, 1 сокет, 1.4 ГБ RAM
  • Developer — полная функциональность, только для разработки
  • Standard, Enterprise — для production
  • Web, Business Intelligence — специализированные редакции

16.2. Совместимость между версиями​

  • База данных можно восстановить только на равной или более новой версии
  • Нельзя восстановить резервную копию SQL Server 2022 на SQL Server 2019
  • Уровень совместимости БД: ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 150; (150 = SQL Server 2019)

16.3. Миграция​

  • Generate Scripts в SSMS (с данными или без)
  • Import and Export Wizard (через SSIS)
  • BACPAC — портативный файл схемы и данных (через SqlPackage.exe)
  • Transactional Replication, Always On AG, Log Shipping

17. Безопасность: углублённо​

17.1. Always Encrypted​

  • Шифрование данных на клиенте
  • Ключи: Column Master Key (CMK), Column Encryption Key (CEK)
  • Поддерживается в .NET через SqlParameter

17.2. Dynamic Data Masking​

  • Маскировка данных для неавторизованных пользователей
  • Типы масок: default, partial, random, email
  • Пример: 
    ALTER TABLE Users ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'default()');

17.3. Row-Level Security (RLS)​

  • Фильтрация строк на уровне запроса
  • Создание функции безопасности и привязка политики
CREATE FUNCTION fn_securitypredicate(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS access WHERE @TenantId = USER_ID();

CREATE SECURITY POLICY TenantPolicy
ADD FILTER PREDICATE fn_securitypredicate(TenantId) ON dbo.Orders;

17.4. Transparent Data Encryption (TDE)​

  • Шифрование файлов БД на диске
  • Защищает от кражи файлов
  • Требует сертификат базы данных

18. Диагностика и устранение неполадок​

18.1. ERRORLOG​

  • Расположение: Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ERRORLOG
  • Просмотр: EXEC xp_readerrorlog

18.2. Default Trace​

  • Включён по умолчанию
  • События: создание/удаление БД, вход в систему, ошибки
  • Путь: SELECT path FROM sys.traces WHERE is_default = 1

18.3. DBCC команды​

  • DBCC CHECKDB — проверка целостности
  • DBCC SQLPERF(LOGSPACE) — использование лога
  • DBCC INPUTBUFFER(spid) — последняя команда сессии
  • DBCC OPENTRAN — активные транзакции
  • DBCC FREEPROCCACHE — очистка кэша планов

19. Работа с JSON​

19.1. Проверка и извлечение​

  • ISJSON(expression) — возвращает 1, если строка корректный JSON
  • JSON_VALUE(json, path) — извлекает скалярное значение
    Пример: JSON_VALUE('{"user": {"name": "Alice"}}', '$.user.name') → 'Alice'
  • JSON_QUERY(json, path) — извлекает объект или массив
    Пример: JSON_QUERY('{"tags": ["a","b"]}', '$.tags') → ["a","b"]

19.2. Модификация​

  • JSON_MODIFY(json, path, newValue)
    Пример: 
    DECLARE @j NVARCHAR(MAX) = '{"name": "Bob"}';
    SET @j = JSON_MODIFY(@j, '$.age', 30);
    -- Результат: {"name": "Bob", "age": 30}

19.3. Преобразование таблицы в JSON​

  • FOR JSON PATH — гибкое формирование структуры 
    SELECT ID, Name FROM Users FOR JSON PATH;
    -- [{"ID":1,"Name":"Alice"},{"ID":2,"Name":"Bob"}]
  • FOR JSON AUTO — автоматическая вложенность по JOIN
  • Опции: ROOT('users'), WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES

19.4. Преобразование JSON в таблицу​

  • OPENJSON(json) — табличная функция 
    SELECT * FROM OPENJSON('[{"id":1,"name":"Alice"}]')
    WITH (ID INT '$.id', Name NVARCHAR(50) '$.name');

20. Работа с XML​

20.1. Тип данных XML​

  • Может быть нетипизированным или привязанным к XSD-схеме (XML(SCHEMA COLLECTION ...))
  • Поддерживает методы: .value(), .query(), .exist(), .modify(), .nodes()

20.2. Методы XML​

  • .value(xpath, sql_type) — извлекает скаляр 
    DECLARE @x XML = '<user><name>Alice</name></user>';
    SELECT @x.value('(/user/name)[1]', 'NVARCHAR(50)');
  • .query(xpath) — возвращает XML-фрагмент
  • .exist(xpath) — проверяет наличие узла (1/0)
  • .modify(xml_dml) — изменяет XML на месте 
    SET @x.modify('insert <age>30</age> as last into (/user)[1]');
  • .nodes(xpath) — разворачивает XML в набор строк для JOIN

20.3. FOR XML​

  • FOR XML RAW — каждая строка как элемент <row>
  • FOR XML AUTO — иерархия по именам таблиц
  • FOR XML EXPLICIT — полный контроль через метаданные
  • FOR XML PATH('User') — гибкое формирование с путями 
    SELECT ID AS 'ID', Name AS 'Info/FullName'
    FROM Users
    FOR XML PATH('User'), ROOT('Users');

21. Производительность: практические рекомендации​

21.1. Индексация​

  • Избегать избыточных индексов — они замедляют INSERT/UPDATE/DELETE
  • Использовать Included Columns вместо расширения ключа
  • Регулярно обновлять статистику: UPDATE STATISTICS dbo.Users
  • Следить за Key Lookups в планах — часто решаются добавлением колонок в INCLUDE

21.2. Параметризация​

  • Всегда использовать параметризованные запросы (sp_executesql)
  • Избегать конкатенации строк — предотвращает SQL-инъекции и улучшает переиспользование планов

21.3. Антипаттерны​

  • SELECT * — извлекает ненужные данные
  • Функции в WHERE над столбцами: WHERE YEAR(OrderDate) = 2025 → нарушает sargability
    Лучше: WHERE OrderDate >= '20250101' AND OrderDate < '20260101'
  • Курсоры вместо множественных операций
  • Вложенные представления без анализа плана

21.4. TempDB​

  • Размещать на быстрых дисках (SSD/NVMe)
  • Предварительно задавать размер файлов, избегая автоувеличения
  • Количество файлов данных: 1 на CPU core до 8, затем 1 на 4–8 ядер

22. Особенности редакций SQL Server​

22.1. Developer Edition​

  • Полностью совпадает с Enterprise по функционалу
  • Лицензируется бесплатно для разработки и тестирования
  • Не допускается использование в production
  • Идеален для локальной разработки, обучения, демонстраций

22.2. Express Edition​

  • Бесплатен для любых сценариев
  • Ограничения:
    • Максимум 10 ГБ на одну БД (только данные, не журнал)
    • Один сокет процессора
    • До 1.4 ГБ RAM для буферного пула
    • Нет SQL Agent (можно эмулировать через Windows Task Scheduler + sqlcmd)
    • Нет SSIS, SSAS, SSRS
  • Подходит для небольших приложений, встраиваемых решений

22.3. Сравнение возможностей​

ФункцияExpressDeveloperStandardEnterprise
In-Memory OLTP❌✅✅ (ограничено)✅
Columnstore Indexes❌✅✅ (ограничено)✅
Partitioning❌✅✅✅
Always On AG❌✅✅ (2 реплики)✅ (9 реплик)
TDE❌✅✅✅
RLS / DDM✅✅✅✅

23. Автоматизация и командная строка​

23.1. sqlcmd​

  • Утилита командной строки для выполнения T-SQL
  • Пример: 
    sqlcmd -S .\SQLEXPRESS -d MyDB -Q "SELECT COUNT(*) FROM Users"
  • Поддержка входных файлов: -i script.sql
  • Переменные: -v TableName="Users"

23.2. PowerShell и SqlServer модуль​

  • Установка: Install-Module -Name SqlServer
  • Командлеты:
    • Invoke-Sqlcmd
    • Get-SqlDatabase
    • Backup-SqlDatabase
    • Restore-SqlDatabase
  • Пример: 
    Invoke-Sqlcmd -ServerInstance ".\SQLEXPRESS" -Database "MyDB" -Query "SELECT * FROM Logs"

23.3. BACPAC и DACPAC​

  • DACPAC — схема базы данных (через SSDT)
  • BACPAC — схема + данные
  • Утилита: SqlPackage.exe 
    SqlPackage.exe /Action:Export /SourceServer:. /SourceDatabase:MyDB /TargetFile:MyDB.bacpac

24. Проектирование и стиль​

24.1. Именование​

  • Схемы: dbo, reporting, staging
  • Таблицы: Users, OrderItems (PascalCase, множественное число)
  • Колонки: UserID, CreatedDate, IsActive
  • Процедуры: usp_GetActiveUsers, usp_UpdateOrderStatus
  • Избегать зарезервированных слов и пробелов

24.2. Нормализация​

  • 1NF: атомарные значения
  • 2NF: отсутствие частичных зависимостей от составного ключа
  • 3NF: отсутствие транзитивных зависимостей
  • Денормализация допустима ради производительности в аналитических системах

24.3. Шаблоны проектирования​

  • Soft Delete: колонка IsDeleted BIT NOT NULL DEFAULT 0
  • Audit Trail: триггер или CDC для логирования изменений
  • Slowly Changing Dimensions (SCD) — тип 2: новая запись при изменении атрибута
  • Lookup Tables: справочники с кодами и описаниями

25. Распространённые задачи: готовые решения​

25.1. Поиск по всем таблицам​

DECLARE @SearchStr NVARCHAR(100) = 'Alice';
DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128);

DECLARE cur CURSOR FOR
SELECT t.name, c.name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.system_type_id IN (167, 175, 231, 239); -- строковые типы

OPEN cur;
FETCH NEXT FROM cur INTO @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('IF EXISTS(SELECT 1 FROM [' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @SearchStr + '%'')
          PRINT ''' + @TableName + '.' + @ColumnName + '''');
    FETCH NEXT FROM cur INTO @TableName, @ColumnName;
END
CLOSE cur; DEALLOCATE cur;

25.2. Получение размера таблиц​

SELECT
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.name, s.name, p.rows
ORDER BY TotalSpaceKB DESC;

25.3. Поиск блокировок​

SELECT
    blocking.session_id AS BlockingSession,
    blocked.session_id AS BlockedSession,
    blocked.wait_time,
    blocked.wait_type,
    blocked.wait_resource,
    sqltext.text AS BlockedQuery
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) sqltext;

26. Сравнение T-SQL с другими диалектами SQL​

26.1. Отличия от стандартного SQL​

  • TOP вместо LIMIT (как в MySQL/PostgreSQL)
  • IDENTITY вместо SERIAL или AUTO_INCREMENT
  • GETDATE() вместо CURRENT_TIMESTAMP (хотя последний тоже поддерживается)
  • ISNULL() вместо COALESCE() (но COALESCE тоже есть и является стандартным)
  • + для конкатенации строк (вместо ||)

26.2. Отличия от PostgreSQL​

  • В PostgreSQL: массивы, JSONB, расширенные оконные функции, CTE с модифицирующими запросами (INSERT ... RETURNING)
  • В SQL Server: PIVOT/UNPIVOT, OUTPUT clause, MERGE, интеграция с Windows, SSIS/SSRS

26.3. Отличия от MySQL​

  • MySQL: AUTO_INCREMENT, ENGINE=InnoDB, GROUP BY с неагрегированными колонками (в старых версиях), LIMIT
  • SQL Server: строгая проверка GROUP BY, обязательное указание схемы при необходимости, отсутствие движков хранения

26.4. Совместимость через ANSI-режим​

  • SQL Server поддерживает большинство ANSI SQL-92/99 конструкций
  • Для переносимости: избегать TOP, использовать ROW_NUMBER() + подзапрос вместо LIMIT

27. Миграция с других СУБД​

27.1. С MySQL​

  • Замена AUTO_INCREMENT → IDENTITY
  • DATETIME в MySQL поддерживает микросекунды — в SQL Server использовать DATETIME2(6)
  • VARCHAR без указания длины в MySQL = TEXT — в SQL Server обязательно указывать длину
  • Экранирование: обратные кавычки ` → квадратные скобки [ ] или двойные кавычки (если QUOTED_IDENTIFIER ON)

27.2. С PostgreSQL​

  • Замена SERIAL → IDENTITY
  • JSONB → NVARCHAR(MAX) + ISJSON() или XML для структурированных данных
  • Массивы → таблицы с внешним ключом или STRING_SPLIT() при хранении как строка
  • Функции: переписать PL/pgSQL на T-SQL или CLR

27.3. Инструменты миграции​

  • SQL Server Migration Assistant (SSMA) — официальный инструмент Microsoft
    • Поддерживает Oracle, MySQL, PostgreSQL, Access, Sybase
    • Конвертирует схему, данные, процедуры
  • Azure Data Studio + расширения — для анализа совместимости

28. In-Memory OLTP (Hekaton)​

28.1. Назначение​

  • Высокопроизводительная обработка транзакций в памяти
  • Устранение блокировок и логгирования на уровне строк

28.2. Требования​

  • Таблица должна быть создана с MEMORY_OPTIMIZED = ON
  • Обязательно наличие по крайней мере одного некластеризованного хэш-индекса или диапазонного индекса
  • Все столбцы фиксированной длины или LOB вне строки

28.3. Создание​

CREATE TABLE dbo.MemoryUsers (
    UserID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    Name NVARCHAR(100) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

28.4. Ограничения​

  • Нет поддержки FOREIGN KEY, CHECK, IDENTITY, TRIGGER
  • Нельзя использовать ALTER TABLE — только DROP и CREATE
  • Максимальный размер базы в памяти ограничен редакцией (Express — 320 МБ)

28.5. Процедуры​

  • Natively compiled stored procedures — компилируются в машинный код
  • Объявляются с WITH NATIVE_COMPILATION, SCHEMABINDING
  • Только обращение к memory-optimized таблицам

29. Резервное копирование в облако​

29.1. Azure Blob Storage​

  • URL-адрес резервной копии: https://mystorage.blob.core.windows.net/mycontainer/backup.bak
  • Требуется Shared Access Signature (SAS) или Storage Account Key
  • Команда: 
    BACKUP DATABASE MyDB
    TO URL = 'https://mystorage.blob.core.windows.net/backups/MyDB.bak'
    WITH CREDENTIAL = 'MyAzureCredential';

29.2. AWS S3​

  • Через промежуточный сервер или PowerShell-скрипты
  • Использование sqlcmd + aws s3 cp после локального бэкапа

29.3. Шифрование резервных копий​

  • BACKUP ... WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert)
  • Требует предварительно созданного сертификата

30. Продвинутый мониторинг​

30.1. Query Store: анализ регрессий​

  • Включить: 
    ALTER DATABASE MyDB SET QUERY_STORE = ON;
  • Просмотр планов: 
    SELECT q.query_id, p.plan_id, rs.count_executions, rs.avg_duration
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id;
  • Принудительное использование плана: 
    EXEC sp_query_store_force_plan @query_id = 10, @plan_id = 2;

30.2. Extended Events: лёгкий профилинг​

  • Создание сессии: 
    CREATE EVENT SESSION [LongQueries] ON SERVER
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.sql_text)
        WHERE duration > 1000000 -- 1 секунда
    )
    ADD TARGET package0.ring_buffer;
  • Запуск: ALTER EVENT SESSION [LongQueries] ON SERVER STATE = START;

30.3. Мониторинг TempDB​

  • Использование: 
    SELECT 
        name,
        size * 8 / 1024 AS SizeMB,
        max_size,
        growth
    FROM tempdb.sys.database_files;
  • Активность: 
    SELECT 
        session_id,
        user_objects_alloc_page_count,
        internal_objects_alloc_page_count
    FROM sys.dm_db_session_space_usage
    WHERE user_objects_alloc_page_count > 0;

30.4. Wait Statistics​

  • Ключевые ожидания:
    • PAGEIOLATCH_* — медленный диск
    • LCK_M_* — блокировки
    • CXPACKET — параллелизм (не всегда плохо)
    • WRITELOG — медленная запись журнала
  • Сброс статистики: DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

31. Настройка автозапуска и служб​

31.1. Службы SQL Server​

  • SQL Server (MSSQLSERVER) — ядро СУБД
  • SQL Server Agent (MSSQLSERVER) — планировщик
  • SQL Server Browser — для именованных экземпляров
  • Управление: services.msc или PowerShell (Start-Service, Stop-Service)

31.2. Автозапуск​

  • По умолчанию: Automatic
  • Для Express Edition без Agent — задачи через Windows Task Scheduler: 
    sqlcmd -S .\SQLEXPRESS -d MyDB -i "C:\Scripts\DailyJob.sql"

31.3. Запуск от учётной записи​

  • Рекомендуется использовать Managed Service Account (MSA) или gMSA в домене
  • Избегать запуска от Local System в production